
clear

foreach year in 1970 1980 1990 2000 2007 2010 2011 2012 2013 2014 2015 2016 {
	


// Load Data

if `year' < =2000 {
	clear
	
	use if year == `year' using "$dir/data/data_public/census_1970_2000.dta"

di `year'
tab year
}


if `year' >2000 {
	use statefip  labforce empstat year   educd occ2010 perwt age  puma classwkr incwage wkswork2 uhrswork sex citizen race ind ind1990 if year == `year' using "$dir/data/data_public/acs_bgyears.dta", clear
	di `year'
tab year
	
}


	// Prepare
	
	keep if age >=15 & age<65
	gen pop  = 1
	
	*labforce ==2 -> in the labor force
	
	gen lforce           =  (labforce == 2)
	
	*want to exclude self-employed CLASSWKR == 1
	
	gen emp = (empstat == 1) if classwkr != 1 
	
	
	*empstat==1 means employed
	
	
	*one more thing: lets check for missing occupation data
	gen emp_occ = 0
	replace emp_occ = emp if occ2010<.
	
	*indicator for if you're employed
	gen emp_ind=0
	replace emp_ind=1 if emp==1

	//Demographics
	gen foreign = 0
	replace foreign = 1 if citizen >0 & citizen <.
	
	gen female = 0
	replace female = 1 if sex == 2
	
	gen female_oas = (female==1 & occ2010 >=5000 & occ2010 <6000) if occ2010!=.
	
	gen nonwhite = 0
	replace nonwhite = 1 if race>1 & race<.
	
	gen black = (race ==2) if race!=.
	gen asian = (race ==4 | race == 5 |race == 6) if race!=.
	
	gen hschool      = (educd>=62) if educd!=. 
	gen some_college = (educd>=64) if educd!=.
	gen some_college_emp     = (educd>=64) if educd!=. & emp == 1
	
	
	gen college = (educd>=101) if educd!=.
	
	gen college_emp = (educd>=101) if educd!=. & emp == 1
	
	
	// Years of Education
	gen     yeduc = 0  if educd <=2
	replace yeduc = 1  if educd <=12 & yeduc==.
	replace yeduc = 4  if educd <=17 & yeduc==.
	replace yeduc = 6  if educd <=23 & yeduc==.
	replace yeduc = 8  if educd <=26 & yeduc==.
	replace yeduc = 9  if educd <=30 & yeduc==.
	replace yeduc = 10 if educd <=40 & yeduc==.
	replace yeduc = 11 if educd <=50 & yeduc==.
	replace yeduc = 12 if educd <=65 & yeduc==.
	replace yeduc = 13 if educd <=71 & yeduc==.
	replace yeduc = 14 if educd <=83 & yeduc==.
	replace yeduc = 15 if educd <=90 & yeduc==.
	replace yeduc = 16 if educd <=101 & yeduc==.
	replace yeduc = 17 if educd <=110 & yeduc==.
	replace yeduc = 18 if educd <=114 & yeduc==.
	replace yeduc = 20 if educd <=116 & yeduc==.
	
	gen yeduc_emp = yeduc if emp == 1
	
	// Potential Experience
	gen yexper =  age-yeduc-6
	
	gen yexper_emp = age-yeduc-6 if emp == 1
	
	
	//Wages
	gen wkswork=0
	replace wkswork = 6.5  if wkswork2==1
	replace wkswork = 20   if wkswork2==2
	replace wkswork = 33   if wkswork2==3
	replace wkswork = 43.5 if wkswork2==4
	replace wkswork = 48.5 if wkswork2==5
	replace wkswork = 51   if wkswork2==6


	gen wage_annual = incwage if emp == 1
	gen wage_weekly = incwage/wkswork  if emp == 1
	gen wage_hourly = incwage/(wkswork*uhrswork)  if emp == 1
	

	gen emp_ftfy = ( emp == 1  & uhrswork>=35 & wkswork2 ==6)
	
	gen wage_hourly_ftfy = incwage/(50*uhrswork)  if emp_ftfy == 1
	
	gen hours = uhrswork if emp == 1
	
	
	*want to create wages for if college grad or non-college grad
		
	
	gen col_emp_ftfy      = emp_ftfy if college == 1
	gen nocol_emp_ftfy    = emp_ftfy if college == 0
	
	gen wage_col_annual   = incwage                     if emp == 1 & college == 1
	gen wage_nocol_annual = incwage                     if emp == 1 & college == 0
	gen wage_col_weekly   = incwage/wkswork             if emp == 1 & college == 1
	gen wage_nocol_weekly = incwage/wkswork             if emp == 1 & college == 0
	gen wage_col_hourly   = incwage/(wkswork*uhrswork)  if emp == 1 & college == 1
	gen wage_nocol_hourly = incwage/(wkswork*uhrswork)  if emp == 1 & college == 0
	
	gen wage_col_hourly_ftfy = incwage/(50*uhrswork)    if emp_ftfy == 1 & college == 1
	gen wage_nocol_hourly_ftfy = incwage/(50*uhrswork)  if emp_ftfy == 1 & college == 0
	
	// Commuting Zone
         // PUMAS only defined for: 1990,2000, 2005-2015.
         gen     puma2000 = real(string(statefip) + "00" + string(puma)) if puma<1000 & year>=1990 
         replace puma2000 = real(string(statefip) + "0" + string(puma)) if puma>=1000 & puma<10000 & year>=1990 
         replace puma2000 = real(string(statefip) + string(puma)) if puma>=10000 & year>=1990 

         gen puma2012 = puma2000 if year>2011
         replace puma2000 = . if year>2011
		 
		 compress

	       
		 joinby puma2000 using "$dir/data/interim/cw_cty_czone_puma2000_afact.dta" , unmatched(master)
         tab _merge
         gen merged = (_merge==3)

         *note, for new orleans, from 2007-2011, 3 pumas were collapsed into one, b/c of low pop
         *so 2277777 is the combination of  2201801,  2201802, and 2201905
         *however all 3 pumas are completely contained within commuting zone 3300, so they each
         *have an afactor of 1

		 replace czone = 3300     if puma2000 == 2277777
		 replace afact = 1 if puma2000 == 2277777

         drop _merge
		 

  if `year' >= 2012 {
		 joinby puma2012 using "$dir/data/interim/cw_cty_czone_puma2012_afact.dta" , unmatched(master) update
         tab _merge
         replace merged = (_merge>=3) if year>2011
         replace merged = 1 if puma == 77777
		 drop _merge
	
  }
	
		 
	if `year' == 1970 {
		
	 rename cntygp97 cty_grp70
//	

		 
		 
		joinby cty_grp70 using "$dir/data/data_public/cw_ctygrp1970_czone_corr.dta", unmatched(master) update
		tab _merge
		replace merged = (_merge==3) if year==1970
		drop _merge

	}
	
	if `year' == 1980  {
		// 1980
		gen ctygrp1980     = real(string(statefip) + "00" + string(cntygp98))  if cntygp98<10
		replace ctygrp1980 = real(string(statefip) + "0" + string(cntygp98))  if cntygp98>=10
		joinby ctygrp1980 using "$dir/data/data_public/cw_ctygrp1980_czone_corr.dta", unmatched(master) update 
		tab _merge
		replace merged = (_merge==3) if year==1980
         tab year merged
         drop _merge
         keep if merged==1

	}		 

         //note that approx 250k observations from 2000  are missing puma information
         //this is 2.6% of the 2000 sample, but have to drop b/c can't match to czones
    *afact is = 0 for 1980 because variable is named afactor
	capture replace afact = afactor if year==1980
	gen new_weight = perwt*afact
	
	*this variable allows us to keep track of nonemployed folks
	gen occ2010_aug=occ2010
	replace occ2010_aug = 0 if emp_ind==0 
	
	*since some czones span state boundaries, want to assign czones to the state that the largest portion of the czone falls in
	*in order to cluster standard errors at the state level
	
	*rule: assign czone to state with the largest share of the czone working age population in 2000, 
	
	merge m:1 czone using "$dir/data/interim/pop_weighted_state_czone.dta"
	
	drop _merge
	
	// Manufacturing
	gen manufac =  (ind1990 >= 100 & ind1990<400) if ind1990!=.
	gen services = (ind1990 >= 721 & ind1990<900) if ind1990!=.
	
	// Women who work
	gen emp_female = (emp==1 & sex==2) 
	gen emp_male = (emp==1 & sex == 1)
	
	gen fem_col_pop = sex == 2 & college == 1
	gen fem_col_emp = sex ==2 & college ==1 & emp == 1
	
	gen wage_fem_col_annual   = incwage                     if emp == 1 & college == 1 & sex == 2
	gen wage_fem_nocol_annual = incwage                     if emp == 1 & college == 0 & sex == 2
	
	
	
	gen fem_nocol_pop = sex == 2 & college == 0
	gen fem_nocol_emp = sex ==2 & college ==0 & emp == 1
	
	gen male_col_pop = sex == 1 & college == 1
	gen male_col_emp = sex ==1 & college ==1 & emp == 1
	
	gen male_nocol_pop = sex == 1 & college == 0
	gen male_nocol_emp = sex ==1 & college ==0 & emp == 1
	
	gen wage_male_col_annual   = incwage                     if emp == 1 & college == 1 & sex == 1
	gen wage_male_nocol_annual = incwage                     if emp == 1 & college == 0 & sex == 1
	
	

	
	collapse yeduc 	yeduc_emp  yexper yexper_emp wage_annual  wage_weekly wage_col_hourly wage_nocol_hourly ///
	wage_col_annual wage_nocol_annual  wage_col_hourly_ftfy  wage_col_weekly wage_nocol_weekly ///
	wage_nocol_hourly_ftfy wage_hourly wage_hourly_ftfy statefip state_assigned (first) occ2010  (sum) hschool (sum) some_college (sum) black  (sum) asian  (sum) female_oas (sum) some_college_emp  (sum) college (sum) college_emp  (sum) pop (sum) emp  (sum) lforce ///
	(sum) foreign (sum) female (sum) nonwhite (sum) hours (sum) emp_ftfy (sum) col_emp_ftfy (sum) nocol_emp_ftfy (sum) manufac (sum) services (sum) emp_female (sum) emp_male ///
	(sum) fem_col_pop (sum) fem_col_emp (mean) wage_fem_col_annual (mean) wage_fem_nocol_annual (sum) fem_nocol_pop (sum) fem_nocol_emp (sum) male_col_pop (sum) male_col_emp ///
	(sum) male_nocol_pop (sum) male_nocol_emp (mean) wage_male_col_annual (mean) wage_male_nocol_annual   [pw=new_weight], by(year occ2010_aug czone) fast
	
	
	
	save "$dir/data/interim/temp`year'", replace
	
}
			 
	
	
	clear
	
	 foreach year in 1970 1980 1990 2000 2007 2010 2011 2012 2013 2014 2015 2016 {
	 	
		use "$dir/data/interim/temp`year'.dta", clear
	
	 
	 

	
	
	//now file is at the czone x year x occupation level
	

	
	
	egen czone_lf = total(emp), by(czone year)
	egen czone_emp = total(emp), by(czone year)
	egen czone_pop  = total(pop), by(czone year)
	  gen off_total = wage_hourly_ftfy*emp_ftfy
	  
	  egen czone_emp_female = total(emp_female), by(czone year)
	  egen czone_pop_female = total(female), by(czone year)
	    egen czone_emp_male = total(emp_male), by(czone year)
	
	gen flfp = czone_emp_female/czone_pop_female
	gen mlfp = czone_emp_male/(czone_pop-czone_pop_female)
	
	// Create ids for each occupation as well as combinations of occupations
	gen mgmt               = (occ2010_aug >=10    & occ2010_aug <430)
	gen bus_fin_op         = (occ2010_aug >=500   & occ2010_aug <1000)
	gen pc_math            = (occ2010_aug >=1000  & occ2010_aug <1300)
	gen arch_eng           = (occ2010_aug >=1300  & occ2010_aug <1600)
	gen phys_scl_scnc      = (occ2010_aug >=1600  & occ2010_aug <2000)
	gen com_scl_srvc       = (occ2010_aug >=2000  & occ2010_aug <2100)
	gen legal              = (occ2010_aug >=2100  & occ2010_aug <2200)
	gen education          = (occ2010_aug >=2200  & occ2010_aug <2600)
	gen arts_dsgn          = (occ2010_aug >=2600  & occ2010_aug <3000)
	gen heal               = (occ2010_aug >=3000  & occ2010_aug <3540)
	
	gen hlth_spprt         = (occ2010_aug >=3600  & occ2010_aug <3700)
	gen protection         = (occ2010_aug >=3700  & occ2010_aug <4000)
	gen food_prep          = (occ2010_aug >=4000  & occ2010_aug <4200)
	gen bldng_mntnce      = (occ2010_aug >=4200  & occ2010_aug <4300)
	gen prsnal_care        = (occ2010_aug >=4300  & occ2010_aug <4700)
	gen sales              = (occ2010_aug >=4700  & occ2010_aug <5000)
	gen oas             = (occ2010_aug >=5000  & occ2010_aug<6000)
	
	gen farm               = (occ2010_aug >=6000  & occ2010_aug<6200)
	gen const              = (occ2010_aug >=6200  & occ2010_aug <6940)
	gen ins_mntnc_rpr      = (occ2010_aug >=7000  & occ2010_aug<7700)
	
	gen production         = (occ2010_aug >=7700  & occ2010_aug <9000)
	gen transport          = (occ2010_aug >=9000  & occ2010_aug <9750)
	
	*want to define aggregated occuaption classifications
	*blue collar: over 50% male and under 40% college degree in 2000:
	//construction, installation, transport, farming, protection, proudction, building maintenance
	*pink collar: over 50% female and under 40% college degree in 2000: [excluding OAS]
	//health support, personal care, foodprep sales
	*white collar male: over 50% male and over 40% college degree in 2000 
	//archetectire, computer occs, mgmt, legal, physical sciences, arts and design
	*white collar female: over 50% femae and over 40% college degree in 2000
	//health, education, community service, business 
	gen blue_collar = const ==1 | ins_mntnc_rpr==1 | transport ==1 | farm ==1 | protection ==1 | production ==1 | bldng_mntnce ==1
	gen pink_collar = hlth_spprt ==1 | prsnal_care==1 | food_prep ==1 | sales ==1
	gen white_m_collar = arch_eng ==1 | pc_math ==1 | mgmt ==1 | legal ==1 | phys_scl_scnc  ==1 | arts_dsgn ==1 
	gen white_f_collar = heal  == 1 | education ==1 | com_scl_srvc  ==1 | bus_fin_op   ==1 
	gen white_collar = white_m_collar==1 | white_f_collar == 1
	gen non_oas = oas ==0 & occ2010_aug>=10



	
// Create vars for all sectors
	foreach occ in const heal mgmt oas bus_fin_op pc_math arch_eng ///
				   phys_scl_scnc com_scl_srvc legal education arts_dsgn ///
				   hlth_spprt protection food_prep bldng_mntnce prsnal_care ///
				   sales farm ins_mntnc_rpr production transport blue_collar ///
				   pink_collar white_m_collar white_f_collar white_collar non_oas  {
				   	
					
		// Occupation Share
		egen `occ'_lf = total(`occ'*emp), by(czone year)
		gen  `occ'_share = `occ'_lf*100/czone_emp
		gen `occ'_pop = `occ'_lf/czone_pop
		gen cz_`occ'_pop_pct = `occ'_pop*100
		// college share (e.g. table D1)
		gen aux_scollege_`occ'     = some_college_emp * `occ'
		egen aux_cz_some_coll_`occ' = total(aux_scollege_`occ'), by(czone year)
		gen cz_some_coll_`occ'_pct = aux_cz_some_coll_`occ'*100/`occ'_lf
		// College
		gen aux_college_`occ' = college_emp * `occ'
		egen cz_college_`occ' = total(aux_college_`occ'), by(czone year)
		gen cz_college_`occ'_pct = cz_college_`occ'*100/`occ'_lf
		// wages for the occupation (eg. table E1) hourly
		gen aux_owh_total_`occ'    = wage_hourly*emp*`occ'
		egen aux_cwh_total_`occ'   = total(aux_owh_total_`occ'), by(czone year)
		gen cz_wage_hrly_`occ' = aux_cwh_total_`occ'/(`occ'_lf)
		// Wages for ftfy
		gen aux_off_total_`occ'              = off_total*`occ'
		egen aux_cff_total_`occ'             = total(aux_off_total_`occ'), by(czone year)
		egen aux_cz_emp_ftfy_`occ'           = total(emp_ftfy*`occ'), by(czone year)
		gen cz_wage_hrly_ftfy_`occ'      = aux_cff_total_`occ'/(aux_cz_emp_ftfy_`occ')
		// Weekly
		gen aux_oww_total_`occ'              = wage_weekly*emp*`occ'
		egen aux_cww_total_`occ'             = total(aux_oww_total_`occ'), by(czone year)
		gen cz_wage_weekly_`occ'         = aux_cww_total_`occ'/(`occ'_lf)
		// Annual
		gen aux_owa_total_`occ'              = wage_annual*emp*`occ'
		egen aux_cwa_total_`occ'             = total(aux_owa_total_`occ'), by(czone year)
		gen cz_wage_annual_`occ'         = aux_cwa_total_`occ'/(`occ'_lf)
		// Annual college
		gen aux_owca_total_`occ'              = wage_col_annual*college_emp*`occ'  
		egen aux_cwca_total_`occ'             = total(aux_owca_total_`occ'), by(czone year)
		gen cz_wg_yr_col`occ'             = aux_cwca_total_`occ'/(cz_college_`occ')
		// Annual no college
		gen aux_ownca_total_`occ'              = wage_nocol_annual*(emp-college_emp)*`occ'  
		egen aux_cwnca_total_`occ'             = total(aux_ownca_total_`occ'), by(czone year)
		gen cz_wg_yr_nocol`occ'            = aux_cwnca_total_`occ'/(`occ'_lf-cz_college_`occ')
	}	
	
	
		*egen czone_lf = total(emp), by(czone year)
		gen aux_scollege     = some_college_emp 
		egen aux_cz_some_coll = total(some_college_emp), by(czone year)
		gen cz_some_coll_pct = aux_cz_some_coll*100/czone_lf
		egen cz_college = total(college_emp), by(czone year)
		gen cz_college_pct = cz_college*100/czone_lf
		// wages for the occupation (eg. table E1) hourly
		gen aux_owh_total    = wage_hourly*emp
		egen aux_cwh_total   = total(aux_owh_total), by(czone year)
		gen cz_wage_hrly = aux_cwh_total/(czone_lf)
		// Wages for ftfy
		egen aux_cff_total             = total(off_total), by(czone year)
		egen aux_cz_emp_ftfy           = total(emp_ftfy), by(czone year)
		gen cz_wage_hrly_ftfy      = aux_cff_total/(aux_cz_emp_ftfy)
		// Weekly
		gen aux_oww_total              = wage_weekly*emp
		egen aux_cww_total             = total(aux_oww_total), by(czone year)
		gen cz_wage_weekly         = aux_cww_total/(czone_lf)
		// Annual
		gen aux_owa_total              = wage_annual*emp
		egen aux_cwa_total             = total(aux_owa_total), by(czone year)
		gen cz_wage_annual         = aux_cwa_total/(czone_lf)
		// Annual college
		gen aux_owca_total              = wage_col_annual*college_emp 
		egen aux_cwca_total             = total(aux_owca_total), by(czone year)
		gen cz_wg_yr_col             = aux_cwca_total/(cz_college)
		// Annual no college
		gen aux_ownca_total              = wage_nocol_annual*(emp-college_emp) 
		egen aux_cwnca_total             = total(aux_ownca_total), by(czone year)
		gen cz_wg_yr_nocol            = aux_cwnca_total/(czone_lf-cz_college)
		
		
	
		*employment outcomes by demo subgroup:  e.g. female, male x col, no col
		
		egen aux_femcol_pop = total(fem_col_pop), by(czone year) 
		egen aux_femcol_emp = total(fem_col_emp), by(czone year)
		gen femcol_pct = aux_femcol_emp/aux_femcol_pop 
		
		gen aux_femcol_wage              = wage_fem_col_annual*fem_col_emp //at czone occ year level, total wages to female college workers 
		egen aux_femcol_wagetot            = total(aux_femcol_wage), by(czone year) //sum across occs, now at czone year level
		gen cz_wg_yr_femcol             = aux_femcol_wagetot/(aux_femcol_emp) //divided by the total number of female college employment at czone year level
		
		
		egen aux_femnocol_pop = total(fem_nocol_pop), by(czone year) 
		egen aux_femnocol_emp = total(fem_nocol_emp), by(czone year)
		gen femnocol_pct = aux_femnocol_emp/aux_femnocol_pop 
		
		gen aux_femnocol_wage              = wage_fem_nocol_annual*fem_nocol_emp 
		egen aux_femnocol_wagetot            = total(aux_femnocol_wage), by(czone year) 
		gen cz_wg_yr_femnocol             = aux_femnocol_wagetot/(aux_femnocol_emp) 
		
		
			egen aux_malecol_pop = total(male_col_pop), by(czone year) 
		egen aux_malecol_emp = total(male_col_emp), by(czone year)
		gen malecol_pct = aux_malecol_emp/aux_malecol_pop 
		
		gen aux_malecol_wage              = wage_male_col_annual*male_col_emp 
		egen aux_malecol_wagetot            = total(aux_malecol_wage), by(czone year) 
		gen cz_wg_yr_malecol             = aux_malecol_wagetot/(aux_malecol_emp) 
		
		
			egen aux_malenocol_pop = total(male_nocol_pop), by(czone year) 
		egen aux_malenocol_emp = total(male_nocol_emp), by(czone year)
		gen malenocol_pct = aux_malenocol_emp/aux_malenocol_pop 
		
		gen aux_malenocol_wage              = wage_male_nocol_annual*male_nocol_emp 
		egen aux_malenocol_wagetot            = total(aux_malenocol_wage), by(czone year) 
		gen cz_wg_yr_malenocol             = aux_malenocol_wagetot/(aux_malenocol_emp) 
		
		
		*new measure: empllyment rates for college non-oas and non-college non-owas
		
		gen aux_nocol = pop-college
		gen aux_nocol_emp = emp - college_emp
		
		gen aux_nonoas_nocol_emp = aux_nocol_emp*non_oas
		egen aux_nonoas_nocol_emp_tot = total(aux_nonoas_nocol_emp), by(czone year) 
		egen aux_nocol_pop = total(aux_nocol), by(czone year)	
		gen nonoas_nocol_pct = aux_nonoas_nocol_emp_tot/aux_nocol_pop
		
		gen aux_oas_nocol_emp = aux_nocol_emp*oas
		egen aux_oas_nocol_emp_tot = total(aux_oas_nocol_emp), by(czone year) 
		gen oas_nocol_pct = aux_oas_nocol_emp_tot/aux_nocol_pop
		
		
		gen aux_nonoas_col_emp = college*non_oas
		egen aux_nonoas_col_emp_tot = total(aux_nonoas_col_emp), by(czone year) 
		egen aux_col_pop = total(college), by(czone year)	
		gen nonoas_col_pct = aux_nonoas_col_emp_tot/aux_col_pop
		
		gen aux_oas_col_emp = college*oas
		egen aux_oas_col_emp_tot = total(aux_oas_col_emp), by(czone year) 
		gen oas_col_pct = aux_oas_col_emp_tot/aux_col_pop
		

	
	// Share of manufacturing and services employment by czone
	egen czone_manufac      = total(manufac), by(czone year)
	gen  czone_sh_manufac   = czone_manufac/czone_lf
	egen czone_services     = total(services), by(czone year)
	gen  czone_sh_services  = czone_services/czone_lf
	egen czone_hschool = total(hschool), by (czone year)
	gen  czone_sh_hschool   = czone_hschool/czone_pop
	
	egen czone_some_college = total(some_college_emp), by(czone year)
	gen  czone_sh_some_college   = czone_some_college/czone_pop
	egen czone_college = total(college_emp), by(czone year)
	gen  czone_sh_college   = czone_college/czone_pop
	
	gen czone_col_sh_nonoas = (czone_college-aux_college_oas)/(czone_lf-oas_lf)
	
	
		
		
		
	gen  czone_sh_lf        = czone_lf/czone_pop
	egen czone_black = total(black), by (czone year)
	gen  czone_sh_black     = czone_black/czone_pop
	egen czone_asian = total(asian), by (czone year)
	gen  czone_sh_asian     = czone_asian/czone_pop
		egen czone_foreign = total(foreign), by (czone year)
	gen  czone_sh_foreign   = czone_foreign/czone_pop
	egen czone_female = total(female), by (czone year)
	egen czone_female_emp = total(emp_female), by (czone year)
	gen czone_sh_fem_emp    = czone_female_emp/czone_female 
	egen czone_female_oas= total(female_oas), by (czone year)
	gen czone_sh_female_oas = czone_female_oas/czone_female_emp
	
	egen czone_nonwhite = total(nonwhite), by (czone year)
	


	gen cz_some_college_all_pct = czone_some_college*100/czone_lf
	gen cz_college_all_pct= czone_college*100/czone_lf
	gen cz_female_all_pct = czone_female*100/czone_lf
	gen cz_foreign_all_pct = czone_foreign*100/czone_lf
	gen cz_nonwhite_all_pct =  czone_nonwhite*100/czone_lf
	
	
		
		

	gen e_pop = czone_emp/czone_pop
	
	drop aux_*
	

	
	keep czone_sh_* cz_* *_share *_lf  wage_* statefip state_assigned emp_female emp_male czone_emp czone_pop 	 czone_lf czone_col_sh_nonoas e_pop flfp mlfp czone year femcol_pct femnocol_pct malecol_pct malenocol_pct nonoas_nocol_pct nonoas_col_pct oas_col_pct oas_nocol_pct


	
collapse  czone_sh_* cz_* *_share   wage_* statefip state_assigned emp_female emp_male czone_emp czone_pop 	czone_lf czone_col_sh_nonoas e_pop flfp mlfp femcol_pct femnocol_pct malecol_pct malenocol_pct nonoas_nocol_pct nonoas_col_pct oas_col_pct oas_nocol_pct  , by(czone year) fast

	

	gen annual_wage_pop = cz_wage_annual*czone_emp/czone_pop
	
	// deflate wages
	merge m:1 year using "$dir/data/interim/wage_deflation.dta"
	drop if _merge == 2
	

	
	gen lwg_annual_r =  ln(cz_wage_annual/cpi)
	gen lwg_weekly_r = ln(cz_wage_weekly/cpi)
	gen lwg_hourly_r = ln( cz_wage_hrly/cpi)
	gen lwg_hourly_ftfy_r = ln(cz_wage_hrly_ftfy/cpi)
	gen lwg_wage_pop_r =  ln(annual_wage_pop/cpi) 
	gen lwg_col_annual_r           =  ln(cz_wg_yr_col/cpi) 
	gen lwg_nocol_annual_r         =  ln(cz_wg_yr_nocol/cpi) 

	foreach occ in 	femnocol femcol malenocol malecol {
		
					gen lwg_yr_`occ'_r          = ln(cz_wg_yr_`occ'/cpi)
		
	}


// Create Log-Wages
	foreach occ in const heal mgmt oas bus_fin_op pc_math arch_eng ///
				   phys_scl_scnc com_scl_srvc legal education arts_dsgn ///
				   hlth_spprt protection food_prep bldng_mntnce prsnal_care ///
				   sales farm ins_mntnc_rpr production transport  blue_collar pink_collar white_m_collar white_f_collar white_collar non_oas   {
				   
	
			gen lwg_hrly_`occ'_r        = ln(cz_wage_hrly_`occ'/cpi)
			gen lwg_hrly_ftfy_`occ'_r   = ln(cz_wage_hrly_ftfy_`occ'/cpi )
			gen lwg_weekly_`occ'_r      = ln(cz_wage_weekly_`occ'/cpi)
			gen lwg_annual_`occ'_r  	   = ln(cz_wage_annual_`occ'/cpi)
			gen lwg_yr_col`occ'_r          = ln(cz_wg_yr_col`occ'/cpi)
			gen lwg_yr_nocol`occ'_r        = ln(cz_wg_yr_nocol`occ'/cpi)
		}
		

	
	gen state = state_assigned
		gen czone_emp_pop = czone_emp/czone_pop 
	label var czone_emp_pop "Employment/Population Ratio"
	
	
	keep lwg_* state czone_emp_pop czone_sh_* cz_* *_share *_lf  statefip state_assigned emp_female emp_male czone_emp czone_pop flfp mlfp	femcol_pct femnocol_pct malecol_pct malenocol_pct nonoas_nocol_pct nonoas_col_pct oas_col_pct oas_nocol_pc  year czone
	
		
		save "$dir/data/interim/temp2`year'.dta", replace
		
		}
		
			clear
	
	 foreach year in 1970 1980 1990 2000 2007 2010 2011 2012 2013 2014 2015 2016 {
	 	
		append using "$dir/data/interim/temp2`year'.dta"
	
	 }
		
		
		

	// Select Years pre and post
	preserve 
		keep if year<2007
		save "$dir/data/interim/outcome_pre_vars_acs.dta", replace
	restore
	
	drop if year <=2006 | year == 2008 | year == 2009
	save "$dir/data/interim/outcome_vars_acs.dta", replace

	
	keep year czone czone_sh_manufac czone_sh_services czone_sh_college czone_sh_foreign czone_sh_fem_emp czone_sh_hschool czone_sh_some_college  czone_sh_lf czone_sh_black czone_sh_asian czone_sh_fem_emp czone_sh_female_oas
	save "$dir/data/interim/control_vars_acs.dta", replace 

